const connection = require("../app/database");

class ArticleService {
  async create(title, author, content, keywords, desc, category_id) {
    console.log(title, author, content, keywords, desc, category_id);
    const statement =
      "INSERT INTO aset_article (title,author,content,keywords,`desc`,category_id ) VALUES (?,?,?,?,?,?);";
    const [result] = await connection.execute(statement, [
      title,
      author,
      content,
      keywords,
      desc,
      category_id,
    ]);
    return result;
  }

  async update(id, title, author, content, keywords, desc, category_id) {
    console.log(title, author, content, keywords, desc, category_id, id);
    const statement =
      "UPDATE aset_article SET title =?,author=?,content=?,keywords=?,`desc`=?,category_id=? WHERE id=?;";
    const [result] = await connection.execute(statement, [
      title,
      author,
      content,
      keywords,
      desc,
      category_id,
      id,
    ]);
    return result;
  }

  async remove(commentId) {
    const statement = `DELETE  FROM asck_article_category WHERE id=?;`;
    const [result] = await connection.execute(statement, [commentId]);
    return result;
  }
  async clientList(category_id, page, pageSize, keywords) {
    let offset = (page - 1) * pageSize;
    let category_str = ``,
      category_str2 = ``;
    if (category_id.trim().length > 0) {
      category_str = ` ar2.category_id = ${category_id} AND`;
      category_str2 = ` ar.category_id = ${category_id} AND`;
    }

    const statement = `SELECT (SELECT COUNT(*)  from aset_article ar2
    LEFT JOIN asck_article_category arc2 on arc2.id=ar2.category_id
    WHERE ar2.state=1 and ${category_str} ar2.title
    LIKE '%${keywords}%')  total,
    JSON_ARRAYAGG(JSON_OBJECT(
    'id',ar.id,'title',ar.title,
    'state',ar.state,
    'keywords',ar.keywords,'pageviews',ar.pageviews,
    'category_name',arc.content,'createAt',ar.createAt,
    'updateAt',ar.updateAt
    )) list
    FROM (SELECT * FROM aset_article LIMIT ${offset},${pageSize}) ar
    LEFT JOIN asck_article_category arc on arc.id=ar.category_id
    WHERE  ar.state=1 and ${category_str2} ar.title
    LIKE '%${keywords}%';`;

    try {
      const [result] = await connection.execute(statement);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }
  async managerList(status, category_id, page, pageSize, keywords) {
    let offset = (page - 1) * pageSize;
    let category_str = ``,
      category_str2 = ``;
    let state_str = ``,
      state_str2 = ``;
    if (category_id.trim().length > 0) {
      category_str = ` ar2.category_id = ${category_id} AND`;
      category_str2 = ` ar.category_id = ${category_id} AND`;
    }

    if (status.trim().length > 0) {
      state_str = `ar2.state= ${status} AND`;
      state_str2 = `ar.state= ${status} AND`;
    }

    const statement = `SELECT (SELECT COUNT(*)  from aset_article ar2
    LEFT JOIN asck_article_category arc2 on arc2.id=ar2.category_id
    WHERE ${state_str} ${category_str} ar2.title
    LIKE '%${keywords}%')  total,
    JSON_ARRAYAGG(JSON_OBJECT(
    'id',ar.id,'title',ar.title,
    'state',ar.state,
    'keywords',ar.keywords,'pageviews',ar.pageviews,
    'category_name',arc.content,'createAt',ar.createAt,
    'updateAt',ar.updateAt
    )) list
    FROM (SELECT * FROM aset_article LIMIT ${offset},${pageSize}) ar
    LEFT JOIN asck_article_category arc on arc.id=ar.category_id
    WHERE  ${state_str2} ${category_str2} ar.title
    LIKE '%${keywords}%';`;

    try {
      const [result] = await connection.execute(statement);
      console.log(result);
      return result[0];
    } catch (error) {
      console.log(error);
    }
  }
}

module.exports = new ArticleService();
